package oims;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import db.DBManager;
import db.DbAccess;
import db.Record;

public class OrderRecord extends DbAccess<OrderRecord> implements Record<OrderRecord> {

	private String customer;
	private java.util.Date date;

	private final String SQL_SELECT_STOCK = "select * from stocks where number = ? for update";
	private final String SQL_UPDATE_STOCK = "update stocks set quantity = ? where number = ?";
	private final String SQL_INSERT_ITEM  = "insert into order_items (orderId,product,price,quantity) values (?,?,?,?)";

	public String[] order ( String customer, java.util.Date date, OrderRecordItem[] items ) {
		System.out.println("order ---");
		System.out.println("customer: " + customer);
		System.out.println("date: " + date);
		System.out.println("item[0]: " + items[0].getProduct());

		this.customer = customer;
		this.date = date;
		int cnt = this.add();

		Connection con = null;
		ArrayList<String> orders = new ArrayList<String>();
		int orderId = 0;

		if (cnt == 1) {
			try {
				//	注文レコード登録時に付番された ID を取得
				String sql = "select id as last from orders order by id desc limit 0,1";
				con = DBManager.getConnection();
				//	自動コミットを無効にする（プログラムで制御する）
				con.setAutoCommit(false);

				Statement stm = con.createStatement();
				ResultSet rs = stm.executeQuery(sql);
				if (rs.next()) {
					orderId = rs.getInt("last");
					orders.add(String.format("%04d", orderId));
				}
				rs.close();
				//	在庫数を減らして（更新して）、注文明細を登録する準備
				PreparedStatement select_stock = con.prepareStatement(SQL_SELECT_STOCK);
				PreparedStatement update_stock = con.prepareStatement(SQL_UPDATE_STOCK);
				PreparedStatement insert_item  = con.prepareStatement(SQL_INSERT_ITEM);
				//	全ての受注商品について繰り返す
				for (int i=0 ; i<items.length ; i++) {
					//	商品を取り出して注文番号を設定する
					OrderRecordItem item = items[i];
					item.setOrderId(orderId);
					//	在庫レコードを取得する
					select_stock.setString(1, item.getProduct());
					ResultSet rs_stock = select_stock.executeQuery();
					if (rs_stock.next()) {
						//	受注後の在庫数を計算する
						int remain 	= rs_stock.getInt("quantity")
									- item.getQuantity();
						//	在庫が不足している場合は受注できない
						if (remain < 0) {
							RuntimeException e = new RuntimeException();
							throw e;
						}
						//	在庫数を減らす
						update_stock.setInt(1, remain);
						update_stock.setString(2, item.getProduct());
						update_stock.executeUpdate();
						//	注文明細を登録する
						insert_item.setString(1, String.format("%04d", orderId));
						insert_item.setString(2, item.getProduct());
						insert_item.setInt(3, item.getPrice());
						insert_item.setInt(4, item.getQuantity());
						insert_item.executeUpdate();
					}
					rs_stock.close();
				}
				select_stock.close();
				update_stock.close();
				insert_item.close();
				//	更新する
				con.commit();
				//	自動コミットを有効に戻す
				con.setAutoCommit(true);

				System.out.println("受注処理を完了しました。");
			}
			catch (SQLException e) {
				System.out.println("受注処理中に例外が発生しました。");
				try {
					con.rollback();
				}
				catch (SQLException ex) {
					ex.printStackTrace();
				}
				this.remove(orderId);
				orders.clear();
				e.printStackTrace();
			}
			catch (RuntimeException e) {
				System.out.println("在庫が不足しているため受注処理を中断しました。");
				try {
					con.rollback();
				}
				catch (SQLException ex) {
					ex.printStackTrace();
				}
				this.remove(orderId);
				orders.clear();
			}
		}
		return (String[])orders.toArray(new String[0]);
	}

	public List<Map<String,String>> getInfo (String number) {
		List<Map<String,String>> list = null;
		try {
			String sql = "select * from orders where id = ?";
			String sql_item = "select * from order_items where orderId = ? order by id";
			Connection con = DBManager.getConnection();

			String customer = null;
			Date date  = null;
			String cond = null;
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, Integer.parseInt(number));
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				list = new ArrayList<Map<String,String>>();

				customer = rs.getString("customer");
				Timestamp ts = rs.getTimestamp("date");
				date = new Date(ts.getTime());
				cond = condition(date);

				PreparedStatement ps_item = con.prepareStatement(sql_item);
				ps_item.setString(1, number);
				ResultSet rs_item = ps_item.executeQuery();
				while (rs_item.next()) {
					Map<String,String> map = new HashMap<String,String>();
					map.put("customer", customer);
					map.put("product", rs_item.getString("product"));
					map.put("price",  String.valueOf(rs_item.getInt("price")));
					map.put("quantity",  String.valueOf(rs_item.getInt("quantity")));
				    SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					map.put("date", (String)sdf.format(date));
					map.put("info", cond);

					list.add(map);
				}
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	private String condition(Date date) {
		Date now = new Date();
		long diff = now.getTime() - date.getTime();

		String cond = null;
		if (diff < (1*60*1000)) {
			cond = "出荷作業中";
		}
		else if (diff < (2*60*1000)) {
			cond = "配達中";
		}
		else {
			cond = "お届け済み";
		}
		return cond;
	}

	/* (非 Javadoc)
	 * @see db.Record#find(int)
	 */
	@Override
	public Record<OrderRecord> find(int id) {
		return find_by_id("orders", id);
	}

	/* (非 Javadoc)
	 * @see db.Record#add()
	 */
	@Override
	public int add() {
		return add("orders", RecordToMap());
	}

	/* (非 Javadoc)
	 * @see db.Record#remove(int)
	 */
	@Override
	public int remove(int id) {
		return remove_by_id("orders", id);
	}

	/* (非 Javadoc)
	 * @see db.Record#update()
	 */
	@Override
	public int update() {
		return update("orders", RecordToMap());
	}

	/* (非 Javadoc)
	 * @see db.DbAccess#ResultSetToRecord(java.sql.ResultSet)
	 */
	@Override
	protected Record<OrderRecord> ResultSetToRecord(ResultSet rs) {
		try {
			super.setId(rs.getInt("id"));
			this.setCustomer(rs.getString("customer"));
			this.setDate(rs.getTimestamp("date"));
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return this;
	}

	/* (非 Javadoc)
	 * @see db.DbAccess#RecordToMap()
	 */
	@Override
	protected Map<String, String> RecordToMap() {
		Map<String, String> map = new HashMap<String, String>();

		map.put("id", String.valueOf(super.getId()));
		if (this.customer != null) map.put("customer", this.customer);
	    SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.JAPANESE);
		map.put("date", (String)sdf.format(this.date));
//		map.put("date", String.valueOf(this.date.getTime()));

		return map;
	}

	/**
	 * @return customer
	 */
	public String getCustomer() {
		return customer;
	}

	/**
	 * @param customer セットする customer
	 */
	public void setCustomer(String customer) {
		this.customer = customer;
	}

	/**
	 * @return date
	 */
	public Date getDate() {
		return date;
	}

	/**
	 * @param date セットする date
	 */
	public void setDate(java.util.Date date) {
		this.date = date;
	}

}
